﻿using System;
using System.Collections.Generic;
using System.Text;
using LibraryONTrack.dto;
using System.Data.SQLite;

namespace LibraryONTrack.dao
{
	[Serializable]
	public class TeacherException : Exception
	{
		public TeacherException() { }
		public TeacherException(string message) : base(message) { }
		public TeacherException(string message, Exception inner) : base(message, inner) { }
		protected TeacherException(
		System.Runtime.Serialization.SerializationInfo info,
		System.Runtime.Serialization.StreamingContext context)
			: base(info, context) { }
	}

	class TeacherDAO : PersonDAO
	{
		public List<Teacher> GetAllTeachers()
		{
			string query = @"SELECT
											   student_teacher_id, name, teacher_id
                       FROM roster
											 WHERE teacher_id is null;";
			SqlDao dao = SqlDao;
			return dao.GetList<Teacher>(dao.GetSQLiteCommand(query));
		}
		
		/// <summary>
		/// Retrieves a teacher by their ID
		/// </summary>
		/// <param name="teacherId"></param>
		/// <returns></returns>
		public Teacher GetById(long teacherId)
		{
			string query = @"SELECT
  										   student_teacher_id, name
                       FROM roster
											 WHERE student_teacher_id = @teacherId and
														 teacher_id is null;";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@teacherId", teacherId));
			return dao.GetSingle<Teacher>(command);
		}

		/// <summary>
		/// Retrieves a teacher by their name (case-insensitive)
		/// </summary>
		/// <param name="teacherName"></param>
		/// <returns></returns>
		public Teacher GetByName(string teacherName)
		{
			string query = @"SELECT
  										   student_teacher_id, name
                       FROM roster
											 WHERE lower(name) = lower(@teacherName) and
														 teacher_id is null;";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@teacherName", teacherName, 64));
			return dao.GetSingle<Teacher>(command);
		}


		/// <summary>
		/// 
		/// </summary>
		/// <param name="studentId"></param>
		/// <returns></returns>
		public Teacher GetTeacher(long id)
		{
			string query = @"SELECT
  										   student_teacher_id, name, teacher_id
                       FROM roster
											 WHERE student_teacher_id = @id and
														 teacher_id is null;";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@id", id));
			return dao.GetSingle<Teacher>(command);
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="studentName"></param>
		/// <returns></returns>
		public Teacher GetTeacher(string name)
		{
			string query = @"SELECT
  										   student_teacher_id, name, teacher_id
                       FROM roster
											 WHERE lower(name) = lower(@name) and
														 teacher_id is null;";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@name", name, 64));
			return dao.GetSingle<Teacher>(command);
		}

		new public void delTeacher(Teacher teacher)
		{
			// Delete all students who are assigned to this teacher
			SqlDao dao = SqlDao;
			string sql = @"delete from [roster]
										 where teacher_id = @teacher_id;";			
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			command.Parameters.Add(dao.CreateParameter("@teacher_id", teacher.Id));
			dao.ExecuteNonQuery(command);

			// And then delete the teacher
			base.Delete(teacher.Id);
		}

		public Teacher AddTeacher(string teacherName)
		{
			if (this.GetTeacher(teacherName) != null)
			{
				throw new TeacherException("Teacher '" + teacherName + "' already exists.");
			}


				SqlDao dao = SqlDao;
				string sql = @"insert into [roster]
										   (name, teacher_id)
										 values
										   (@teacherName, null);
										 select last_insert_rowid();";
				SQLiteCommand command = dao.GetSQLiteCommand(sql);
				command.Parameters.Add(dao.CreateParameter("@teacherName", teacherName, 64));
				return new Teacher((long)dao.ExecuteScalar(command), teacherName);
		}		
	}
}
